#===========================================================================
#
# populate_matches.py
#
# Python script to query mysql database to determine the
# nearest neigbours within search radius between catalogues
# only save the nearest neighbour, not all within search radius
#
#===========================================================================
#
# S. Weston
# AUT University
# Nov 2013
#===========================================================================

def pm():

    print "\nStarting finding nearest neighbours between catalogues\n"

# Connect to the local database with the atlas uid

    db=_mysql.connect(host="localhost",user="atlas",passwd="atlas")

# Lets run a querry

    print field,field,swire_schema,sr,ra1,ra2,dec1,dec2

    print "Truncate the matches table\n"
	
    db.query("truncate table "+survey+".nn_"+field+"_int_matches;")
    db.query("truncate table "+survey+".nn_"+field+"_matches;")
    db.query("set session wait_timeout=30000;")
    db.query("set session interactive_timeout=30000;")
	
    print "First find all the NN candidates with in the search radius: "+str(sr)
#   Put into temporary table
	
    sql1=("insert into "+survey+".nn_"+field+"_int_matches(cid,swire_index_spitzer,dx,dy,r_arcsec,flux) "
              "select t1.cid, "
              "t2.Index_Spitzer, "
              "(t1.RA_Deg-t2.RA_SPITZER)*cos(t1.Dec_Deg), "
              "t1.Dec_Deg-t2.DEC_SPITZER, "
              "sqrt(pow((t1.RA_Deg-t2.RA_SPITZER)*cos(t1.Dec_Deg),2)+pow(t1.Dec_Deg-t2.DEC_SPITZER,2))*3600, "
              "t2.irac_3_6_micron_flux_mujy "
              "from "+survey+".atlasdr3_fullcmpcat as t1, "+swire_schema+".swire as t2 "
              "where pow((t1.RA_Deg-t2.RA_SPITZER)*cos(t1.Dec_Deg),2)+" 
              "pow(t1.Dec_Deg-t2.DEC_SPITZER,2) <= pow("+str(sr)+"/3600,2) "
              " and   t2.ra_spitzer > "+str(ra1)+" and t2.ra_spitzer < "+str(ra2)+" "
              " and   t2.dec_spitzer > "+str(dec1)+" and t2.dec_spitzer < "+str(dec2)+" limit 0,3000000; ")

#    sql='select * from '+field+'.matches;'
    print sql1,"\n"
	
    print "This SQL will take a while .... \n"
    db.query(sql1)

# Create a view

    sql2="drop view "+survey+".min_r_arcsec;"
    sql3="create view "+survey+".min_r_arcsec as \
          select  cid,min(r_arcsec) as min_r_arcsec \
          FROM "+survey+".nn_"+field+"_int_matches  \
          group by cid \
          order by cid;"
    
    print "Drop the view min_r_arcsec \n"
    print sql2
    db.query(sql2)        
    print "Create the view min_r_arcsec \n"
    print sql3
    db.query(sql3)
    
# Select from view and temporary table to get matches (correct cid & swire_index_spitzer)

    sql4="select t1.cid,t1.swire_index_spitzer,t1.dx,t1.dy,t1.r_arcsec,t1.flux \
          from "+survey+".nn_"+field+"_int_matches  t1 , "+survey+".min_r_arcsec  v1 \
          where t1.cid=v1.cid \
          and t1.r_arcsec=v1.min_r_arcsec \
          order by t1.cid;"

    print sql4
    db.query(sql4)
    
# store_result() returns the entire result set to the client immediately.
# The other is to use use_result(), which keeps the result set in the server 
#and sends it row-by-row when you fetch.

#r=db.store_result()
# ...or...
    r=db.use_result()

# fetch results, returning char we need float !

    rows=r.fetch_row(maxrows=0)

# Close connection to the database

    db.close()

# Connect to the local database with the atlas uid

    db=_mysql.connect(host="localhost",user="atlas",passwd="atlas")
    
    for row in rows:
#        print row
        cid=row[0]
        swire_index_spitzer=int(row[1])
        dx=float(row[2])
        dy=float(row[3])
        r_arcsec=float(row[4])
        flux=float(row[5])
        
#        print cid,swire_index_spitzer,dx,dy,r_arcsec,flux
        
        # Update the final matches table     

        sql5="insert into "+survey+".nn_"+field+"_matches(cid,swire_index_spitzer,dx,dy,r_arcsec,flux) \
              values ('%s','%d','%f','%f','%f','%f');" % (cid,swire_index_spitzer,dx,dy,r_arcsec,flux)  
#        print sql5
        db.query(sql5)
    
    db.close()
	
    print "End of populate matches\n"

